package com.ph.irp.base.assistantmaterial.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialDeliveryaddressDao;
import com.ph.irp.model.PBaseAssistantmaterialDeliveryaddress;
import com.ph.util.DBUtil;

public class PBaseAssistantmaterialDeliveryaddressDaoImpl implements
		PBaseAssistantmaterialDeliveryaddressDao {
	private ComboPooledDataSource dataSource;
	
	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void addDeliveryaddress(
			PBaseAssistantmaterialDeliveryaddress deliveryAddress)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		StringBuilder sb = new StringBuilder();
		sb.append("insert into p_base_assistantmaterial_deliveryaddress (DeliveryAddressCode,DeliveryAddressName) values(");
		sb.append("'").append(deliveryAddress.getDeliveryAddressCode().trim()).append("',");
		sb.append("'").append(deliveryAddress.getDeliveryAddressName().trim()).append("')");
		
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.execute();
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		}  
		finally{
				DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@Override
	public void deleteDeliveryaddress(int deliveryAddressId)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		String sql = "delete from p_base_assistantmaterial_deliveryaddress where DeliveryAddressID=?";
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, deliveryAddressId);
			pStmt.execute();
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		}  
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@Override
	public PBaseAssistantmaterialDeliveryaddress getDeliveryaddressById(
			int deliveryAddressId) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		PBaseAssistantmaterialDeliveryaddress deliveryAddress = null;
		String sql = "select * from p_base_assistantmaterial_deliveryaddress where DeliveryAddressID=?";
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, deliveryAddressId);
			rs = pStmt.executeQuery();
			if(rs.next()){
				deliveryAddress = new PBaseAssistantmaterialDeliveryaddress();
				deliveryAddress.setDeliveryAddressId(rs.getInt("DeliveryAddressID"));
				deliveryAddress.setDeliveryAddressCode(rs.getString("DeliveryAddressCode"));
				deliveryAddress.setDeliveryAddressName(rs.getString("DeliveryAddressName"));
			}
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		}   
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
		return deliveryAddress;
	}

	@Override
	public List<PBaseAssistantmaterialDeliveryaddress> getDeliveryaddressList()
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialDeliveryaddress> list = new ArrayList<PBaseAssistantmaterialDeliveryaddress>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_assistantmaterial_deliveryaddress");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while(rs.next()){
				PBaseAssistantmaterialDeliveryaddress pbada = new PBaseAssistantmaterialDeliveryaddress();
				pbada.setDeliveryAddressId(rs.getInt("DeliveryAddressID"));
				pbada.setDeliveryAddressCode(rs.getString("DeliveryAddressCode"));
				pbada.setDeliveryAddressName(rs.getString("DeliveryAddressName"));
				list.add(pbada);
			}
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		} 
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

	@SuppressWarnings("finally")
	@Override
	public boolean isSame(String deliveryAddressCode) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		Boolean b=null;
		String sql = "select * from p_base_assistantmaterial_deliveryaddress where DeliveryAddressCode='"+deliveryAddressCode.trim()+"'";
		
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			rs = pStmt.executeQuery();
			if(rs.next()){
				b=true;
			}else{
				b=false;
			}
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		} 
		finally {
				DBUtil.setNullObject(con, pStmt, rs);

		}
		return b;
	}

	@Override
	public void updateDeliveryaddress(
			PBaseAssistantmaterialDeliveryaddress deliveryAddress)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		StringBuilder sb = new StringBuilder();
		sb.append("update p_base_assistantmaterial_deliveryaddress set DeliveryAddressCode='");
		sb.append(deliveryAddress.getDeliveryAddressCode().trim()).append("',DeliveryAddressName='");
		sb.append(deliveryAddress.getDeliveryAddressName().trim()).append("'");
		sb.append(" where DeliveryAddressID=").append(deliveryAddress.getDeliveryAddressId());
		
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		}  
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List getDeliveryaddressByCode(String code) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialDeliveryaddress> list = new ArrayList<PBaseAssistantmaterialDeliveryaddress>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_assistantmaterial_deliveryaddress where DeliveryAddressCode like '%"+code+"%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while(rs.next()){
				PBaseAssistantmaterialDeliveryaddress pbada = new PBaseAssistantmaterialDeliveryaddress();
				pbada.setDeliveryAddressId(rs.getInt("DeliveryAddressID"));
				pbada.setDeliveryAddressCode(rs.getString("DeliveryAddressCode"));
				pbada.setDeliveryAddressName(rs.getString("DeliveryAddressName"));
				list.add(pbada);
			}
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		} 
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List getDeliveryaddressByCodeAndName(String name, String code)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialDeliveryaddress> list = new ArrayList<PBaseAssistantmaterialDeliveryaddress>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_assistantmaterial_deliveryaddress where DeliveryAddressCode like '%"+code+"%' and DeliveryAddressName like '%"+name+"%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while(rs.next()){
				PBaseAssistantmaterialDeliveryaddress pbada = new PBaseAssistantmaterialDeliveryaddress();
				pbada.setDeliveryAddressId(rs.getInt("DeliveryAddressID"));
				pbada.setDeliveryAddressCode(rs.getString("DeliveryAddressCode"));
				pbada.setDeliveryAddressName(rs.getString("DeliveryAddressName"));
				list.add(pbada);
			}
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		} 
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List getDeliveryaddressByName(String name) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialDeliveryaddress> list = new ArrayList<PBaseAssistantmaterialDeliveryaddress>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_assistantmaterial_deliveryaddress where DeliveryAddressName like '%"+name+"%'");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while(rs.next()){
				PBaseAssistantmaterialDeliveryaddress pbada = new PBaseAssistantmaterialDeliveryaddress();
				pbada.setDeliveryAddressId(rs.getInt("DeliveryAddressID"));
				pbada.setDeliveryAddressCode(rs.getString("DeliveryAddressCode"));
				pbada.setDeliveryAddressName(rs.getString("DeliveryAddressName"));
				list.add(pbada);
			}
		}catch(Exception e){
		   throw new Exception(e.getMessage());
		} 
		finally {
				DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

}
